﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class DocGiaDAO
    {
        #region 1. Them
        public static bool ThemDocGia(DocGiaDTO docgia){
            bool result = false;
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@SoThe", docgia.SoThe));
                sqlParams.Add(new SqlParameter("@HoTen", docgia.HoTen));
                sqlParams.Add(new SqlParameter("@NgaySinh", docgia.NgaySinh));
                sqlParams.Add(new SqlParameter("@DiaChi", docgia.DiaChi));
                sqlParams.Add(new SqlParameter("@Email", docgia.Email));
                sqlParams.Add(new SqlParameter("@MaLoaiDG", docgia.MaLoaiDG));
                sqlParams.Add(new SqlParameter("@NgayCap", docgia.NgayCap));
                sqlParams.Add(new SqlParameter("@NgayHetHan", docgia.NgayHetHan));
                sqlParams.Add(new SqlParameter("@TongTienNo", docgia.TongTienNo));
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertDOCGIum", sqlParams);
                if (n == 1)
                {
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
        #endregion
        #region 2. Sua
        public static bool SuaDocGia(DocGiaDTO docgia){
            bool result = false;
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@SoThe", docgia.SoThe));
                sqlParams.Add(new SqlParameter("@HoTen", docgia.HoTen));
                sqlParams.Add(new SqlParameter("@NgaySinh", docgia.NgaySinh));
                sqlParams.Add(new SqlParameter("@DiaChi", docgia.DiaChi));
                sqlParams.Add(new SqlParameter("@Email", docgia.Email));
                sqlParams.Add(new SqlParameter("@MaLoaiDG", docgia.MaLoaiDG));
                sqlParams.Add(new SqlParameter("@NgayCap", docgia.NgayCap));
                sqlParams.Add(new SqlParameter("@NgayHetHan", docgia.NgayHetHan));
                sqlParams.Add(new SqlParameter("@TongTienNo", docgia.TongTienNo));

                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_UpdateDOCGIum", sqlParams);
                if (n == 1)
                {
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #region 3. Xoa
        public static bool XoaDocGia(String sothe){
            bool result = false;
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@SoThe", sothe));

                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_DeleteDOCGIum", sqlParams);
                if (n == 1)
                {
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
        #endregion
        #region 4. Tim kiem
        public static DocGiaDTO TimDocGia(String sothe){
            DocGiaDTO result = new DocGiaDTO();
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@SoThe", sothe));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectDOCGIum", sqlParams);

                DataRow dr = dt.Rows[0];

                result.SoThe = dr["SoThe"].ToString();
                result.HoTen = dr["HoTen"].ToString();
                result.NgaySinh = DateTime.Parse(dr["NgaySinh"].ToString());
                result.DiaChi = dr["DiaChi"].ToString();
                result.Email = dr["Email"].ToString();
                result.MaLoaiDG = dr["MaLoaiDG"].ToString();
                result.NgayCap = DateTime.Parse(dr["NgayCap"].ToString());
                result.NgayHetHan = DateTime.Parse(dr["NgayHetHan"].ToString());
                result.TongTienNo = decimal.Parse(dr["TongTienNo"].ToString());
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
        public static DataTable TimTatCaDocGia()
        {
            DataTable dt= new DataTable();
            try
            {
                 dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectDOCGIAsAll");
                               
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static List<DocGiaDTO> TimDocGiaByLoaiDG(String maloaidg){
            List<DocGiaDTO> result = new List<DocGiaDTO>();
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaLoaiDG", maloaidg));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectDOCGIAsByAndMaLoaiDG", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    DocGiaDTO tmp = new DocGiaDTO();

                    tmp.SoThe = dr["SoThe"].ToString();
                    tmp.HoTen = dr["HoTen"].ToString();
                    tmp.NgaySinh = DateTime.Parse(dr["NgaySinh"].ToString());
                    tmp.DiaChi = dr["DiaChi"].ToString();
                    tmp.Email = dr["Email"].ToString();
                    tmp.MaLoaiDG = dr["MaLoaiDG"].ToString();
                    tmp.NgayCap = DateTime.Parse(dr["NgayCap"].ToString());
                    tmp.NgayHetHan = DateTime.Parse(dr["NgayHetHan"].ToString());
                    tmp.TongTienNo = decimal.Parse(dr["TongTienNo"].ToString());

                    result.Add(tmp);
                }
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
        public static string TimNguoiMuonTaiLieu(String matailieu){
            String nguoimuon = "";
            try
            {
                SqlDataAccessHelper.ConnectionString = SqlDataAccessHelper.ReadConnectionString("ConnectionString.xml");
                SqlConnection connect = new SqlConnection(SqlDataAccessHelper.ConnectionString);
                connect.Open();
                SqlCommand command = connect.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "TimNguoiMuonTaiLieu";
                command.Parameters.Add(new SqlParameter("@MaTaiLieu", matailieu));
                SqlParameter outparam = new SqlParameter();
                outparam.ParameterName = "@MaThe";
                outparam.DbType = DbType.String;
                outparam.Size = 10;
                outparam.Direction = ParameterDirection.Output;
                command.Parameters.Add(outparam);
                command.UpdatedRowSource = UpdateRowSource.OutputParameters;
                command.ExecuteNonQuery();
                nguoimuon = command.Parameters["@MaThe"].Value.ToString();
                connect.Close();
            }
            catch (System.Exception ex)
            {
            	
            }
            return nguoimuon;
        }
        public static bool CheckDocGiaByID(String sothe){
            bool result = false;
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@SoThe", sothe));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectDOCGIum", sqlParams);

                if (dt.Rows.Count > 0)
                {
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
        public static DateTime TimNgayHetHanCuaThe(String sothe){
            DateTime result = new DateTime();
            try{
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@sothe", sothe));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_Timhancuathedocgia", sqlParams);
                result = DateTime.Parse(dt.Rows[0]["NgayHetHan"].ToString());
            }
            catch(Exception ex){}
            return result;
        }
        public static int TimSoSachDaMuonTrongKhoangThoiGian(String sothe, DateTime cantren, DateTime canduoi){
            int result = 0;
            try
            {
                SqlConnection connect = new SqlConnection(SqlDataAccessHelper.ReadConnectionString("ConnectionString.xml"));
                connect.Open();
                SqlCommand command = connect.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "usp_Tinhsosachdamuontrongkhoangthoigian";
                command.Parameters.AddWithValue("@SoThe", sothe);
                command.Parameters.AddWithValue("@cantren", cantren);
                command.Parameters.AddWithValue("@canduoi", canduoi);
                SqlParameter outparam = new SqlParameter();
                outparam.ParameterName = "@sosach";
                outparam.DbType = DbType.Int32;
                outparam.Direction = ParameterDirection.Output;
                command.Parameters.Add(outparam);

                command.UpdatedRowSource = UpdateRowSource.OutputParameters;
                command.ExecuteNonQuery();
                result = Int32.Parse(command.Parameters["@ngaymuon"].Value.ToString());
                connect.Close();
            }
            catch (System.Exception ex)
            {
            	
            }
            return result;
        }
        #endregion
    }
}
